package com.hnqcgczy.supplier.dao.impl;

import com.hnqcgczy.supplier.dao.MarketDao;
import com.hnqcgczy.supplier.db.MyTransaction;
import com.hnqcgczy.supplier.utils.BaseDao;
import com.hnqcgczy.supplier.vo.Market;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MarketDaoImpl extends BaseDao implements MarketDao {

    public MarketDaoImpl(MyTransaction myTransaction) {
        super(myTransaction);
    }

    @Override
    public int add(Market market) throws SQLException {
        //1.准备sql
        String sql = "INSERT INTO `t_market`(`market_id`, `company_id`, `main_client`, `main_competitor`, `supplier`, `prize`, `factory`, `factory_type`, `chain_listed_company`, `out_listed_company`, `last_year_turnover`, `last_year_profit`) VALUES (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        //2.调用公共方法update
        int i = super.update(sql, market.getCompanyId(), market.getMainClient(), market.getMainCompetitor(), market.getSupplier(), market.getPrize(), market.getFactory(), market.getFactoryType(), market.getChainListedCompany(), market.getOutListedCompany(), market.getLastYearTurnover(), market.getLastYearProfit());
        //3.关闭资源
        super.closeResource();
        return i;
    }

    @Override
    public int update(Market market) throws SQLException {
        //1.准备sql
        String sql = "UPDATE `t_market` SET `company_id` = ?,  `main_client` = ?, `main_competitor` = ?, `supplier` = ?, `prize` = ?, `factory` = ?, `factory_type` = ?, `chain_listed_company` = ?, `out_listed_company` = ?, `last_year_turnover` = ?, `last_year_profit` = ? WHERE `market_id` = ?";
        //2.调用公共方法update
        int i = super.update(sql, market.getCompanyId(), market.getMainClient(), market.getMainCompetitor(), market.getSupplier(), market.getPrize(), market.getFactory(), market.getFactoryType(), market.getChainListedCompany(), market.getOutListedCompany(), market.getLastYearTurnover(), market.getLastYearProfit(), market.getMarketId());
        //3.关闭资源
        super.closeResource();
        return i;
    }

    @Override
    public int delete(Integer marketId) throws SQLException {
        String sql = "delete from `t_market` where `market_id` = ?";
        int i = super.update(sql, marketId);
        super.closeResource();
        return i;
    }

    @Override
    public Market findOne(Integer marketId) throws SQLException {
        String sql = "select market_id,company_id,main_client,main_competitor,supplier,prize,factory,factory_type,chain_listed_company,out_listed_company,last_year_turnover,last_year_profit from `t_market` where `market_id` = ?";
        ResultSet rs = super.query(sql, marketId);
        if (rs != null) {
            while (rs.next()) {
                Market market = new Market();
                //把数据库中查出来的内容填充到实体当中
                market.setMarketId(rs.getInt("market_id"));
                market.setCompanyId(rs.getInt("company_id"));
                market.setMainClient(rs.getString("main_client"));
                market.setMainCompetitor(rs.getString("main_competitor"));
                market.setSupplier(rs.getString("supplier"));
                market.setPrize(rs.getString("prize"));
                market.setFactory(rs.getString("factory"));
                market.setFactoryType(rs.getString("factory_type"));
                market.setChainListedCompany(rs.getString("chain_listed_company"));
                market.setOutListedCompany(rs.getString("out_listed_company"));
                market.setLastYearTurnover(rs.getDouble("last_year_turnover"));
                market.setLastYearProfit(rs.getDouble("last_year_profit"));
                return market;
            }
        }
        return null;
    }

    @Override
    public int total(Market market) throws SQLException {
        //1.拼sql语句
        StringBuffer sb = new StringBuffer("select count(*) totals from `t_market` where 1=1 ");
        if (market != null) {
            //主要客户 main_client
            if (market.getMainClient() != null && !"".equals(market.getMainClient())) {
                sb.append(" and main_client like '%").append(market.getMainClient()).append("%'");
            }
            //工厂类型factory_type
            if (market.getFactoryType() != null && !"".equals(market.getFactoryType())) {
                sb.append(" and factory_type like '%").append(market.getFactoryType()).append("%'");
            }
        }
        String sql = sb.toString();
        ResultSet rs = super.query(sql);
        int totals = 0;
        if (rs != null) {
            while (rs.next()) {
                totals = rs.getInt("totals");
            }
        }
        super.closeResource();
        return totals;
    }

    @Override
    public List<Market> queryByLike(Market market, int start, int size) throws SQLException {
        List<Market> lists = null;
        //分成两部分
        //1.拼sql语句
        StringBuffer sb = new StringBuffer("select market_id,company_id,main_client,main_competitor,supplier,prize,factory,factory_type,chain_listed_company,out_listed_company,last_year_turnover,last_year_profit from `t_market` where 1=1 ");
        if (market != null) {
            //主要客户 main_client
            if (market.getMainClient() != null && !"".equals(market.getMainClient())) {
                sb.append(" and main_client like '%").append(market.getMainClient()).append("%'");
            }
            //工厂类型factory_type
            if (market.getFactoryType() != null && !"".equals(market.getFactoryType())) {
                sb.append(" and factory_type like '%").append(market.getFactoryType()).append("%'");
            }
        }
        sb.append(" limit ").append(start).append(",").append(size);

        //2.和之前一样操作
        String sql = sb.toString();
        System.out.println(sql);
        ResultSet rs = super.query(sql);
        if (rs != null) {
            lists = new ArrayList<>();
            while (rs.next()) {
                Market temp = new Market();
                //把数据库中查出来的内容填充到实体当中
                temp.setMarketId(rs.getInt("market_id"));
                temp.setCompanyId(rs.getInt("company_id"));
                temp.setMainClient(rs.getString("main_client"));
                temp.setMainCompetitor(rs.getString("main_competitor"));
                temp.setSupplier(rs.getString("supplier"));
                temp.setPrize(rs.getString("prize"));
                temp.setFactory(rs.getString("factory"));
                temp.setFactoryType(rs.getString("factory_type"));
                temp.setChainListedCompany(rs.getString("chain_listed_company"));
                temp.setOutListedCompany(rs.getString("out_listed_company"));
                temp.setLastYearTurnover(rs.getDouble("last_year_turnover"));
                temp.setLastYearProfit(rs.getDouble("last_year_profit"));
                //
                lists.add(temp);
                temp = null;
            }
        }
        return lists;
    }


}
